<?php
namespace app\examScore\controller;
use app\common\Base;
use app\common\Excel;
use think\Db;
//--进步奖
class ExamJinbuPrize extends Base {
	
	public function index() {
		$fields = "班级,学号,姓名,总分,班排,级排,上次级排,升幅";
		$list = explode(",", $fields);
		$this->assign('list',$list);
		return $this->fetch();
	}

	public function pageQuery() {
		$termId = input("termId");
		$examId = input("examId");
		$grade = input("grade");
		$clazz = input("clazz");
		if(!$examId) return [];
		if($grade) {
			$array = explode("_", $grade);
			$grade = $array[0];
		}
		$gradeId = intval(input("gradeId"));
		$page = input("page");
		$rows = input("rows");
		$sort = input("sort");
		$order = input("order");
		$orderby = "班级,学号";
		if($sort) $orderby = $sort." ".$order;
		if(!empty($clazz)) $clazz = $gradeId*100 + intval($clazz);
		$examCount = Db::table("考试名称")
					 ->where("学期ID=".$termId." and 年级ID=".$gradeId)
					 ->count();
		$preExamId = 0;
		if(!($examCount == 1 && ($gradeId==1||$gradeId==4))) {
			if($examCount == 1) {
				$preWhere["年级ID"] = array("eq",intval($gradeId) - 1);
				$preWhere["学期ID"] = array("lt", $termId);
			}else {
				$preWhere["年级ID"] = array("eq", $gradeId);
				$preWhere["ID"] = array("lt", $examId);
			}
			$preWhere["考试类型"] = array("eq", 1);
			$preExam = Db::table("考试名称")
						 ->field("max(ID) ID")
						 ->where($preWhere)
						 ->find();
			$preExamId = $preExam["ID"];
		}
		$subsql = Db::table("成绩".$grade)
				  ->field("学生ID,级排 as 上次级排")
				  ->where("考试ID=".$preExamId)
				  ->buildSql();
		$result = Db::table("成绩".$grade." a")
				  ->join([$subsql=>'b'],"a.学生ID=b.学生ID","LEFT")
				  ->field("班级,学号,姓名,总分,班排,级排,上次级排,上次级排-级排 as 升幅")
				  ->where("a.考试ID=".$examId." and (上次级排-级排>=100 or 级排<100 and 上次级排>=级排*2)".(empty($clazz) ? "" : " and 班级=".$clazz))
				  ->order($orderby)
				  ->page($page, $rows)
				  ->select();
		$count = Db::table("成绩".$grade." a")
				  ->join([$subsql=>'b'],"a.学生ID=b.学生ID","LEFT")
				  ->where("a.考试ID=".$examId." and (上次级排-级排>=100 or 级排<100 and 上次级排>=级排*2)".(empty($clazz) ? "" : " and 班级=".$clazz))
				  ->count();
		$grid["rows"] = selectResult($result);
		$grid["total"] = $count;
		return $grid;
	}

	public function exportExcel() {
		$termId = input("termId");
		$examId = input("examId");
		$grade = input("grade");
		$clazz = input("clazz");
		if(!$examId) return [];
		if($grade) {
			$array = explode("_", $grade);
			$grade = $array[0];
		}
		$gradeId = intval(input("gradeId"));
		$page = input("page");
		$rows = input("rows");
		$sort = input("sort");
		$order = input("order");
		$orderby = "班级,学号";
		if($sort) $orderby = $sort." ".$order;
		if(!empty($clazz)) $clazz = $gradeId*100 + intval($clazz);
		$preWhere["学期ID"] = $termId;
		$preWhere["年级ID"] = $gradeId;
		$preWhere["ID"] = array("lt", $examId);
		$preWhere["考试类型"] = array("eq", 1);
		$preExamId = Db::table("考试名称")
					 ->field("max(ID) ID")
					 ->where($preWhere)
					 ->find();
		$preExamId = empty($preExamId["ID"]) ? 0 : $preExamId["ID"];
		$subsql = Db::table("成绩".$grade)
				  ->field("学生ID,级排 as 上次级排")
				  ->where("考试ID=".$preExamId)
				  ->buildSql();
		$result = Db::table("成绩".$grade." a")
				  ->join([$subsql=>'b'],"a.学生ID=b.学生ID","LEFT")
				  ->field("班级,学号,姓名,总分,班排,级排,上次级排,上次级排-级排 as 升幅")
				  ->where("a.考试ID=".$examId." and (上次级排-级排>=100 or 级排<100 and 上次级排>=级排*2)".(empty($clazz) ? "" : " and 班级=".$clazz))
				  ->order($orderby)
				  ->select();
		$result = selectResult($result);
		//excel表头内容
		$fields = "班级,学号,姓名,总分,班排,级排,上次级排,升幅";
		$list = explode(",", $fields);
		$header = [];
		foreach ($list as $key => $value){
			$header[$value] = $value;
		}
		Excel::toExcel($result,'进步奖',$header);
		exit;
	}
}
